﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;

namespace QuanLySieuThi_08520114_08520220
{
    class controller_chungtu
    {
        private SqlCeDataAdapter temp_adapter;
        private DataTable temp_table;
        private SqlCeConnection con = null;

        public DataTable list_ct()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select *,k.MaKho,n.MaNhanVien From CHUNGTU c left join KHO k on (c.KhoID = k.KhoID) left join NHANVIEN n on (c.NhanVienID = n.NhanVienID)";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public DataTable list_ctct(string ct_id)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select c.*,m.MaMatHang,m.NhaPhanPhoi From CHITIETCHUNGTU c Left join MatHang m on (c.MatHangID = m.MatHangID) where ChungTuID = '" + ct_id + "'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public string get_id(string ma,string ten_table,string hauto)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select "+hauto+"ID From "+ten_table+" where Ma"+hauto+" ='" + ma + "'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table.Rows[0][hauto+"ID"].ToString();

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return "";
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return "";
            }
            return "";
        }

        public bool add_ct(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From CHUNGTU Where MaChungTu ='" + data[0] + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã Chứng Từ đã có trong database !", "Thông báo");
                        return false;
                    }
                    string loai = "";
                    if (data[6] == "Nhập")
                        loai = "0";
                    else
                        loai = "1";
                    cmd.CommandText = "Insert Into CHUNGTU (MaChungTu,KhoID,NhanVienID,TenNhanVien,NgayLap,NgayNhapXuatKho,LoaiChungTu) VALUES ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + loai + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_ctct(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    string temp = "";
                    for (int i = 0; i < 4; i++)
                    {
                        if (i == 0)
                            temp = "'" + data[0] + "'";
                        else
                            temp = temp + ",'" + data[i] + "'";
                    }
                    sql = "Insert Into CHITIETCHUNGTU (ChungTuID,MatHangID,TenMatHang,SoLuongNhapXuat) VALUES (" + temp + ")";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public DataTable get_infor(string kho_id,string mh_id)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From KHO_MATHANG Where KhoID = '"+kho_id+"' AND MatHangID = '" +mh_id+"'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public bool luu_kho(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    if (data[4] == "Nhập")
                    {
                        string sql = "";
                        sql = "Select Count(*) From KHO_MATHANG Where KhoID ='" + data[0] + "' and MatHangID ='"+data[1]+"'";
                        SqlCeCommand cmd = new SqlCeCommand(sql, con);
                        int count = (int)cmd.ExecuteScalar();

                        if (count > 0)
                        {
                            cmd.CommandText = "Select SoLuongTonHienTai From KHO_MATHANG Where KhoID ='" + data[0] + "' and MatHangID ='" + data[1] + "'";
                            long sl = (long)cmd.ExecuteScalar();
                            long soluong = sl + Convert.ToInt64(data[2]);
                            cmd.CommandText = "Update KHO_MATHANG set SoLuongNhap = '" + data[2] + "', SoLuongTonHienTai = '" + soluong + "' , NgayNhapKho = '" + data[3] + "' Where KhoID ='" + data[0] + "' and MatHangID ='" + data[1] + "'";
                            cmd.ExecuteNonQuery();
                        }
                        else
                        {
                            cmd.CommandText = "Insert into KHO_MATHANG (KhoID,MatHangID,SoLuongNhap,SoLuongTonHienTai,SoLuongTonToiThieu,NgayNhapKHo) Values ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[2] + "','0','" + data[3] +"')";
                            cmd.ExecuteNonQuery();
                        }
                    }
                    else
                    {
                        string sql = "";
                        sql = "Select Count(*) From KHO_MATHANG Where KhoID ='" + data[0] + "' and MatHangID ='" + data[1] + "'";
                        SqlCeCommand cmd = new SqlCeCommand(sql, con);
                        int count = (int)cmd.ExecuteScalar();
                        if (count > 0)
                        {
                            cmd.CommandText = "Select SoLuongTonHienTai From KHO_MATHANG Where KhoID ='" + data[0] + "' and MatHangID ='" + data[1] + "'";
                            long sl = (long)cmd.ExecuteScalar();
                            long soluong = sl - Convert.ToInt64(data[2]);
                            cmd.CommandText = "Update KHO_MATHANG set SoLuongNhap = '" + data[2] + "', SoLuongTonHienTai = '" + soluong + "' , NgayXuatKho = '" + data[3] + "' Where KhoID ='" + data[0] + "' and MatHangID ='" + data[1] + "'";
                            cmd.ExecuteNonQuery();
                        }
                    }


                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool del_ct(string ct_id)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Delete From CHITIETCHUNGTU Where ChungTuID = '" + ct_id + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "Delete From CHUNGTU Where ChungTuID = '" + ct_id + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

    }
}
